/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.home.dao;

import com.home.common.Constants;
import com.home.common.utils.HibernateUtil;
import com.home.mapping.Agent;
import com.home.mapping.Scorecard;
import com.home.mapping.ScorecardDetails;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.joda.time.DateTime;


/**
 *
 * @author pnduy
 */
public class ScorecardDao extends BaseDao{
    
    public List<Scorecard> findScorecardsOfAgentsInPeriod(List<Integer> listAgentIds, Date dateFrom, Date dateTo, Integer scorecardType) {
        List<Scorecard> list = new ArrayList<Scorecard>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Calendar cal1 = Calendar.getInstance();
            cal1.setTime(dateFrom);
            DateTime dFrom = new DateTime(cal1.get(Calendar.YEAR),cal1.get(Calendar.MONTH) + 1, cal1.get(Calendar.DATE), 0,0,0);
            Calendar cal2 = Calendar.getInstance();
            cal2.setTime(dateTo);
            DateTime dTo = new DateTime(cal2.get(Calendar.YEAR),cal2.get(Calendar.MONTH) + 1, cal2.get(Calendar.DATE), 23,59,59);
            
            Query query = null;
            if (scorecardType > 0) {
                query = session.createSQLQuery("SELECT * FROM scorecard WHERE (scorecard.evalutation_date BETWEEN :dateFrom AND :dateTo) "
                    + "AND scorecard.fk_agent_AGENT IN ("+StringUtils.join(listAgentIds, ",")+") "
                    + "AND scorecard.fk_scorecard_type = :scorecardType")
                    .addEntity(Scorecard.class)
                    .setParameter("dateFrom", dFrom.toDate())
                    .setParameter("dateTo", dTo.toDate())
                    .setParameter("scorecardType", scorecardType);
            } else {
                query = session.createSQLQuery("SELECT * FROM scorecard WHERE (scorecard.evalutation_date BETWEEN :dateFrom AND :dateTo) "
                    + "AND scorecard.fk_agent_AGENT IN ("+StringUtils.join(listAgentIds, ",")+")")                    
                    .addEntity(Scorecard.class)
                    .setParameter("dateFrom", dFrom.toDate())
                    .setParameter("dateTo", dTo.toDate());                    
            }
            
            list = query.list();
            for (Scorecard sc : list) {
                Hibernate.initialize(sc.getAgent());
                Hibernate.initialize(sc.getSubject());
                Hibernate.initialize(sc.getScorecardType());
            }
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return list;
    }
    
    public Integer addNewScorecard(Scorecard scorecard) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Integer scorecardId = 0;
        try {
            scorecardId = (Integer)session.save(scorecard);
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        return scorecardId;
    }
    
    public boolean updateScorecard(Scorecard scorecard) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction trans = null;
        boolean returnValue = true;
        try {
            trans = session.beginTransaction();
            session.update(scorecard);
            trans.commit();
            
        }catch (Exception e) {
            e.printStackTrace();
            returnValue = false;
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        return returnValue;
    }
    
    
    public List<Scorecard> findScorecardsByAgent(Integer agentId) {
        List<Scorecard> list = new ArrayList<Scorecard>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Query query = session.createSQLQuery("SELECT * FROM scorecard WHERE scorecard.fk_agent_AGENT = :agentId")
                    .addEntity(Scorecard.class)
                    .setParameter("agentId", agentId);
                    
            list = query.list();
            for (Scorecard sc : list) {
                Hibernate.initialize(sc.getSubject());
            }
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return list;
    }
    
    public Scorecard findScorecardByIdIncludeAgentSubject(Integer scorecardId) {
        Scorecard scorecard = new Scorecard();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            scorecard = (Scorecard)session.load(Scorecard.class, scorecardId);
            Hibernate.initialize(scorecard.getAgent());
            Hibernate.initialize(scorecard.getSubject());                 
            
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return scorecard;
    }
    
    public boolean deteteScorecard(Scorecard scorecard) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction trans = null;
        boolean returnValue = true;
        try {
            trans = session.beginTransaction();
            // delete scorecard details
            new ScorecardDetailDao().deleteScorecardDetailsByScorecardId(scorecard.getIdScorecard());
            new ScorecardDetailsResultDao().deleteScorecardDetailsResultByScorecardId(scorecard.getIdScorecard());
            new ScorecardGroupAttributeResultDao().deleteByScorecardId(scorecard.getIdScorecard());
            // delete scorecard
            session.createSQLQuery("DELETE FROM scorecard WHERE id_scorecard = :scorecardId")
                    .addEntity(ScorecardDetails.class)
                    .setParameter("scorecardId", scorecard.getIdScorecard())
                    .executeUpdate();
            trans.commit();
            
        }catch (Exception e) {
            e.printStackTrace();
            trans.rollback();
            returnValue = false;
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        return returnValue;
    }
    
    public List<Scorecard> findScorecardsInPeriod(Date dateFrom, Date dateTo, Integer scorecardTypeId) {
        List<Scorecard> list = new ArrayList<Scorecard>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Calendar cal1 = Calendar.getInstance();
            cal1.setTime(dateFrom);
            DateTime dFrom = new DateTime(cal1.get(Calendar.YEAR),cal1.get(Calendar.MONTH) + 1, cal1.get(Calendar.DATE), 0,0,0);
            Calendar cal2 = Calendar.getInstance();
            cal2.setTime(dateTo);
            DateTime dTo = new DateTime(cal2.get(Calendar.YEAR),cal2.get(Calendar.MONTH) + 1, cal2.get(Calendar.DATE), 23,59,59);
            Query query = session.createSQLQuery("SELECT * FROM scorecard WHERE (scorecard.evalutation_date BETWEEN :dateFrom AND :dateTo) "
                    + "AND scorecard.fk_scorecard_type = :scorecardTypeId")
                    .addEntity(Scorecard.class)
                    .setParameter("dateFrom", dFrom.toDate())
                    .setParameter("dateTo", dTo.toDate())
                    .setParameter("scorecardTypeId", scorecardTypeId);
            if (query.list().size() > 0) {
                list = query.list();
            }
            for (Scorecard sc : list) {
                Hibernate.initialize(sc.getAgent().getTeam().getVenture());               
            }
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return list;
    }
    
    public List<Object[]> getAttributeGroupResult(Date dateFrom, Date dateTo, Integer scorecardTypeId) {
        List list = new ArrayList();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            Calendar cal1 = Calendar.getInstance();
            cal1.setTime(dateFrom);
            DateTime dFrom = new DateTime(cal1.get(Calendar.YEAR),cal1.get(Calendar.MONTH) + 1, cal1.get(Calendar.DATE), 0,0,0);
            Calendar cal2 = Calendar.getInstance();
            cal2.setTime(dateTo);
            DateTime dTo = new DateTime(cal2.get(Calendar.YEAR),cal2.get(Calendar.MONTH) + 1, cal2.get(Calendar.DATE), 23,59,59);
            Query query = session.createSQLQuery("SELECT " +
                                                "	venture.venture_name, " +
                                                "	attribute_group.attribute_group_name, " +
                                                "	(AVG(group_result.score)/attribute_group.attribute_group_weight * 100) as group_average " +
                                                "FROM scorecard_group_attribute_result AS group_result " +
                                                "LEFT JOIN attribute_group ON group_result.fk_attribute_group = attribute_group.id_attribute_group " +
                                                "LEFT JOIN scorecard ON group_result.fk_scorecard = scorecard.id_scorecard " +
                                                "LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent " +
                                                "LEFT JOIN team ON agent.fk_team = team.id_team " +
                                                "LEFT JOIN venture ON team.fk_venture = venture.id_venture " +
                                                "WHERE (scorecard.evalutation_date BETWEEN :dateFrom AND :dateTo) " +
                                                "AND (scorecard.fk_scorecard_type = :scorecardTypeId) "+
                                                "GROUP BY venture.venture_name, attribute_group.attribute_group_name")
                    .setParameter("dateFrom", dFrom.toString("YYYY-MM-dd HH:mm:ss"))
                    .setParameter("dateTo", dTo.toString("YYYY-MM-dd HH:mm:ss"))
                    .setParameter("scorecardTypeId", scorecardTypeId);
            if (query.list().size() > 0) {
                list = query.list();
            }
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return list;
    }
    
    public List<Object[]> getAvgScoreOfVentureInWeeks(int weekFrom, int weekTo, Integer scorecardTypeId, Integer ventureId) {
        List list = new ArrayList();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            
            Query query = session.createSQLQuery("SELECT scorecard.week, AVG(scorecard.total_score) FROM scorecard\n" +
                                                "LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent \n" +
                                                "LEFT JOIN team ON agent.fk_team = team.id_team \n" +
                                                "LEFT JOIN venture ON team.fk_venture = venture.id_venture \n" +
                                                "WHERE (scorecard.week BETWEEN :weekFrom AND :weekTo) \n" +
                                                "AND (scorecard.fk_scorecard_type = :scorecardTypeId)\n" +
                                                "AND (venture.id_venture = :ventureId)\n" +
                                                "GROUP BY venture.id_venture, scorecard.week")
                    .setParameter("weekFrom", weekFrom)
                    .setParameter("weekTo", weekTo)
                    .setParameter("scorecardTypeId", scorecardTypeId)
                    .setParameter("ventureId", ventureId);
            if (query.list().size() > 0) {
                list = query.list();
            }
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return list;
    }
    
    public List<Object[]> findRankOfAgentInWeeks(int weekFrom, int weekTo, Integer scorecardTypeId, Integer ventureId) {
        List list = new ArrayList();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            
            Query query = session.createSQLQuery("SELECT agent.agent_name, AVG(scorecard.total_score) FROM scorecard " +
                                                    "LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent " +
                                                    "LEFT JOIN team ON agent.fk_team = team.id_team " +
                                                    "LEFT JOIN venture ON team.fk_venture = venture.id_venture " +
                                                    "WHERE (scorecard.week BETWEEN :weekFrom AND :weekTo) " +
                                                    "AND (scorecard.fk_scorecard_type = :scorecardTypeId) " +
                                                    "AND (venture.id_venture = :ventureId) " +
                                                    "GROUP BY agent.id_agent " +
                                                    "ORDER BY AVG(scorecard.total_score) DESC")
                    .setParameter("weekFrom", weekFrom)
                    .setParameter("weekTo", weekTo)
                    .setParameter("scorecardTypeId", scorecardTypeId)
                    .setParameter("ventureId", ventureId);
            if (query.list().size() > 0) {
                list = query.list();
            }
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return list;
    }
    
    public List<Object[]> findAverageScoreReportWeek(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int year, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("SELECT scorecard.subject_name, CONCAT(scorecard.week, '-', scorecard.year), AVG(scorecard.total_score) FROM scorecard ");
            } else {
                queryStr.append("SELECT agent.agent_name, CONCAT(scorecard.week, '-', scorecard.year), AVG(scorecard.total_score) FROM scorecard ");
            }
            if (group == Constants.GROUP_TYPE_AGENT) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");            
            } else if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");            
            } else {
                queryStr.append("LEFT JOIN subject ON scorecard.fk_subject = subject.id_subject ");            
            }
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
                queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            }
            queryStr.append("WHERE ");            
            queryStr.append("(scorecard.week BETWEEN :periodFrom AND :periodTo) ");
            queryStr.append("AND (scorecard.year = :year) "); 
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (venture.id_venture = :ventureId) ");
            }
            if (chanelId.longValue() > -1) {
                queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            }
            
            
                if (group == Constants.GROUP_TYPE_QA) {
                    queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, scorecard.week ");
                } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                    queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, scorecard.week ");
                } else if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                    queryStr.append("AND (scorecard.fk_subject IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_subject, scorecard.week ");
                } else {
                    queryStr.append("AND (scorecard.fk_agent_AGENT IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_agent_AGENT, scorecard.week ");
                }
            
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("ORDER BY scorecard.subject_name, scorecard.week ASC");
            } else {            
                queryStr.append("ORDER BY agent.agent_name,scorecard.week ASC");
            }
            
            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("year", year);
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                query.setParameter("ventureId", ventureId);
            }
            if (chanelId.longValue() > -1) {
                query.setParameter("chanelId", chanelId);
            }
            
            if (query.list().size() > 0) {
                list = query.list();
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return list;
    }
    
    public List<Object[]> findAverageScoreReportMonth(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int yearFrom, int yearTo, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("SELECT scorecard.subject_name, CONCAT(scorecard.month, '-', scorecard.year), AVG(scorecard.total_score) FROM scorecard ");
            } else {
                queryStr.append("SELECT agent.agent_name, CONCAT(scorecard.month, '-', scorecard.year), AVG(scorecard.total_score) FROM scorecard ");
            }
            if (group == Constants.GROUP_TYPE_AGENT) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");            
            } else if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");            
            } else {
                queryStr.append("LEFT JOIN subject ON scorecard.fk_subject = subject.id_subject ");            
            }
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
                queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            }
            queryStr.append("WHERE ");            
            queryStr.append("(scorecard.month BETWEEN :periodFrom AND :periodTo) ");
            queryStr.append("AND (scorecard.year BETWEEN :yearFrom AND :yearTo) "); 
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (venture.id_venture = :ventureId) ");
            }
            if (chanelId.longValue() > -1) {
                queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            }
            
            
                if (group == Constants.GROUP_TYPE_QA) {
                    queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, scorecard.month ");
                } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                    queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, scorecard.month ");
                } else if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                    queryStr.append("AND (scorecard.fk_subject IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_subject, scorecard.month ");
                } else {
                    queryStr.append("AND (scorecard.fk_agent_AGENT IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_agent_AGENT, scorecard.month ");
                }
            
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("ORDER BY scorecard.subject_name, scorecard.month ASC");
            } else {            
                queryStr.append("ORDER BY agent.agent_name,scorecard.month ASC");
            }
            
            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("yearFrom", yearFrom);
            query.setParameter("yearTo", yearTo);
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                query.setParameter("ventureId", ventureId);
            }
            if (chanelId.longValue() > -1) {
                query.setParameter("chanelId", chanelId);
            }
//            if (group != Constants.GROUP_TYPE_ALL) {
//                query.setParameter("groupIdValues", StringUtils.join(groupIdValues, ","));
//            }
            
            if (query.list().size() > 0) {
                list = query.list();
            }
            
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return list;
    }
    
    public List<Object[]> findAverageScoreReportQuarter(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int yearFrom, int yearTo, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("SELECT scorecard.subject_name, CONCAT(scorecard.quarter, '-', scorecard.year), AVG(scorecard.total_score) FROM scorecard ");
            } else {
                queryStr.append("SELECT agent.agent_name, CONCAT(scorecard.quarter, '-', scorecard.year), AVG(scorecard.total_score) FROM scorecard ");
            }
            if (group == Constants.GROUP_TYPE_AGENT) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");            
            } else if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");            
            } else {
                queryStr.append("LEFT JOIN subject ON scorecard.fk_subject = subject.id_subject ");            
            }
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
                queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            }
            queryStr.append("WHERE ");            
            queryStr.append("(scorecard.quarter BETWEEN :periodFrom AND :periodTo) ");
            queryStr.append("AND (scorecard.year BETWEEN :yearFrom AND :yearTo) "); 
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (venture.id_venture = :ventureId) ");
            }
            if (chanelId.longValue() > -1) {
                queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            }
            
            
                if (group == Constants.GROUP_TYPE_QA) {
                    queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, scorecard.quarter ");
                } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                    queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, scorecard.quarter ");
                } else if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                    queryStr.append("AND (scorecard.fk_subject IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_subject, scorecard.quarter ");
                } else {
                    queryStr.append("AND (scorecard.fk_agent_AGENT IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_agent_AGENT, scorecard.quarter ");
                }
            
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("ORDER BY scorecard.subject_name, scorecard.quarter ASC");
            } else {            
                queryStr.append("ORDER BY agent.agent_name,scorecard.quarter ASC");
            }
            
            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("yearFrom", yearFrom);
            query.setParameter("yearTo", yearTo);
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                query.setParameter("ventureId", ventureId);
            }
            if (chanelId.longValue() > -1) {
                query.setParameter("chanelId", chanelId);
            }
            
            if (query.list().size() > 0) {
                list = query.list();
            }
            
        } catch (Exception e) {
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return list;
    }
    
    public List<Object[]> findTotalEvaluationReportWeek(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int year, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            queryStr.append("SELECT agent.agent_name, CONCAT(scorecard.week, '-', scorecard.year), COUNT(*) numberScorecard ");
            queryStr.append("FROM scorecard ");
            if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");
            } else {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");
            }
            queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
            queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            queryStr.append("WHERE ");
            queryStr.append("(scorecard.week BETWEEN :periodFrom AND :periodTo) ");
            queryStr.append("AND (scorecard.year = :year) ");
            queryStr.append("AND (venture.id_venture = :ventureId) ");

            if (chanelId.longValue() > -1) {
                queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            }
            if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, scorecard.week ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, scorecard.week ");
            }

            queryStr.append("ORDER BY agent.agent_name, scorecard.week ASC");

            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("year", year);
            query.setParameter("ventureId", ventureId);

            if (chanelId.longValue() > -1) {
                query.setParameter("chanelId", chanelId);
            }

            if (query.list().size() > 0) {
                list = query.list();
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }

        return list;
    }
    
    public List<Object[]> findTotalEvaluationReportMonth(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int yearFrom, int yearTo, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            queryStr.append("SELECT agent.agent_name, CONCAT(scorecard.month, '-', scorecard.year), COUNT(*) numberScorecard ");
            queryStr.append("FROM scorecard ");
            if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");
            } else {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");
            }
            queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
            queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            queryStr.append("WHERE ");
            queryStr.append("(scorecard.month BETWEEN :periodFrom AND :periodTo) ");
            queryStr.append("AND (scorecard.year BETWEEN :yearFrom AND :yearTo) "); 
            queryStr.append("AND (venture.id_venture = :ventureId) ");

            if (chanelId.longValue() > -1) {
                queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            }
            if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, scorecard.month ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, scorecard.month ");
            }

            queryStr.append("ORDER BY agent.agent_name, scorecard.month ASC");
            
            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("yearFrom", yearFrom);
            query.setParameter("yearTo", yearTo);
            query.setParameter("ventureId", ventureId);
            if (chanelId.longValue() > -1) {
                query.setParameter("chanelId", chanelId);
            }
            
            if (query.list().size() > 0) {
                list = query.list();
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return list;
    }
    
    public List<Object[]> findTotalEvaluationReportQuarter(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int yearFrom, int yearTo, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            queryStr.append("SELECT agent.agent_name, CONCAT(scorecard.quarter, '-', scorecard.year), COUNT(*) numberScorecard ");
            queryStr.append("FROM scorecard ");
            if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");
            } else {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");
            }
            queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
            queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            queryStr.append("WHERE ");
            queryStr.append("(scorecard.quarter BETWEEN :periodFrom AND :periodTo) ");
            queryStr.append("AND (scorecard.year BETWEEN :yearFrom AND :yearTo) "); 
            queryStr.append("AND (venture.id_venture = :ventureId) ");

            if (chanelId.longValue() > -1) {
                queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            }
            if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, scorecard.quarter ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                queryStr.append(StringUtils.join(groupIdValues, ","));
                queryStr.append(")) ");
                queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, scorecard.quarter ");
            }

            queryStr.append("ORDER BY agent.agent_name, scorecard.quarter ASC");
            
            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("yearFrom", yearFrom);
            query.setParameter("yearTo", yearTo);
            query.setParameter("ventureId", ventureId);
            if (chanelId.longValue() > -1) {
                query.setParameter("chanelId", chanelId);
            }
            
            if (query.list().size() > 0) {
                list = query.list();
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return list;
    }
    
    public List<Object[]> findDetailEvaluationReportWeek(int periodFrom, int periodTo, Integer chanelId, int group, List<Integer> groupIdValues, int year, Integer ventureId) {
        List<Object[]> list = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        try {
            if (groupIdValues.size() <= 0) {
                groupIdValues.add(0);
            }
            StringBuilder queryStr = new StringBuilder();
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("SELECT * FROM scorecard ");
            } else {
                queryStr.append("SELECT agent.agent_name, CONCAT(scorecard.week, '-', scorecard.year), AVG(scorecard.total_score) FROM scorecard ");
            }
            if (group == Constants.GROUP_TYPE_AGENT) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_AGENT = agent.id_agent ");
            } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_TEAMLEAD = agent.id_agent ");            
            } else if (group == Constants.GROUP_TYPE_QA) {
                queryStr.append("LEFT JOIN agent ON scorecard.fk_agent_EVALUATOR = agent.id_agent ");            
            } else {
                queryStr.append("LEFT JOIN subject ON scorecard.fk_subject = subject.id_subject ");            
            }
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("LEFT JOIN team ON agent.fk_team = team.id_team ");
                queryStr.append("LEFT JOIN venture ON team.fk_venture = venture.id_venture ");
            }
            queryStr.append("WHERE ");            
            queryStr.append("(scorecard.week BETWEEN :periodFrom AND :periodTo) ");
            queryStr.append("AND (scorecard.year = :year) "); 
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("AND (venture.id_venture = :ventureId) ");
            }
            if (chanelId.longValue() > -1) {
                queryStr.append("AND (scorecard.fk_scorecard_type = :chanelId) ");
            }
            
            
                if (group == Constants.GROUP_TYPE_QA) {
                    queryStr.append("AND (scorecard.fk_agent_EVALUATOR IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_agent_EVALUATOR, scorecard.week ");
                } else if (group == Constants.GROUP_TYPE_TEAM_LEAD) {
                    queryStr.append("AND (scorecard.fk_agent_TEAMLEAD IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_agent_TEAMLEAD, scorecard.week ");
                } else if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                    queryStr.append("AND (scorecard.fk_subject IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_subject, scorecard.week ");
                } else {
                    queryStr.append("AND (scorecard.fk_agent_AGENT IN (");
                    queryStr.append(StringUtils.join(groupIdValues, ","));
                    queryStr.append(")) ");
                    queryStr.append("GROUP BY scorecard.fk_agent_AGENT, scorecard.week ");
                }
            
            if (group == Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                queryStr.append("ORDER BY scorecard.subject_name, scorecard.week ASC");
            } else {            
                queryStr.append("ORDER BY agent.agent_name,scorecard.week ASC");
            }
            
            Query query = session.createSQLQuery(queryStr.toString());
            query.setParameter("periodFrom", periodFrom);
            query.setParameter("periodTo", periodTo);
            query.setParameter("year", year);
            if (group != Constants.GROUP_TYPE_TYPE_OF_INQUIRY) {
                query.setParameter("ventureId", ventureId);
            }
            if (chanelId.longValue() > -1) {
                query.setParameter("chanelId", chanelId);
            }
            
            if (query.list().size() > 0) {
                list = query.list();
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null && session.isOpen()) {
                session.close();
            }
        }
        
        return list;
    }
}
